﻿CREATE PROCEDURE [dbo].[CreateDIUser] (
	@UserName nvarchar(50),
	@UserPassword nvarchar(50),
	@FirstName nvarchar(50),
	@SecondName nvarchar(50),
	@LastName nvarchar(50),
	@NewUserId int OUTPUT
)
AS
DECLARE @strMsg nvarchar(500), @intRet int, @intErrCode int, @strDBName nvarchar(50), @strExecSQL nvarchar(1000)
DECLARE @intNextId int
	SET NOCOUNT ON

IF EXISTS (SELECT * FROM sys.database_principals WHERE Type = 'S' AND Name = @UserName )
BEGIN
	SET @intErrCode = 2
	SET @strMsg = N'Пользователь %s уже существует в базе данных!'
	RAISERROR (@strMsg, 11, 1, @UserName)
	RETURN @intErrCode
END

IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE Name = @UserName )
BEGIN
	SET @strDBName = Db_Name()

	SELECT @strExecSQL = N'CREATE LOGIN [' + @UserName + N'] WITH PASSWORD=N''' + @UserPassword + N''', 
DEFAULT_DATABASE=['+ @strDBName + N'], 
DEFAULT_LANGUAGE=[русский], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
'
	execute(@strExecSQL)
--	EXEC @intRet = master..sp_addlogin @UserName, @UserPassword, @strDBName, N'русский'
--	IF @intRet <> 0 RETURN @intErrCode

	SET @intErrCode = @@error
	IF @intErrCode <> 0 RETURN @intErrCode
END

--CREATE USER @UserName FOR LOGIN @UserName
--EXEC sp_adduser @UserName, @UserName, NULL

SELECT @strExecSQL = N'CREATE USER [' + @UserName + N'] FOR LOGIN [' + @UserName + N']'
execute(@strExecSQL)

SET @intErrCode = @@error
IF @intErrCode <> 0 RETURN @intErrCode
--TODO удалить когда будет готово рапределение пользователей по ролям
EXEC sp_addrolemember N'db_owner', @UserName

SET @intErrCode = @@error
IF @intErrCode <> 0 RETURN @intErrCode

--получение значения идентификатора
EXEC @intRet = dbo.GetNextCounterFor 'Common', @intNextId OUTPUT
IF @intRet <> 0 RETURN @intRet

INSERT INTO [CD].[DepartmentUsers](Id, [Login],[FirstName],[SecondName],[LastName])
VALUES (@intNextId, @UserName,@FirstName,@SecondName,@LastName)

SET @NewUserId = @intNextId

RETURN 0

